package com.ideal.manage.esms.util;


import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;


/**
 * DB通用服务
 * @author Kit.zheng 2015年9月5日
 *
 */
public class DBUtils {
	
	private static Logger logger = LoggerFactory.getLogger(DBUtils.class);
	
	public static Connection getConnection(){
		String driverClass = Configure.getInstance().getProperty("spring.datasource.driver-class-name");
		String dbUrl = Configure.getInstance().getProperty("spring.datasource.url");
		String dbUser = Configure.getInstance().getProperty("spring.datasource.username");
		String dbPwd = Configure.getInstance().getProperty("spring.datasource.password");
		
		Connection connection = null;
		try {
			Class.forName(driverClass).newInstance();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		try {
			connection = DriverManager.getConnection(dbUrl, dbUser, dbPwd);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return connection;
	}
	
	public static void dbClose(Connection con, Statement stmt, ResultSet rs){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(stmt!=null){
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(con!=null){
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void dbClose(Connection con, Statement stmt){
		
		if(stmt!=null){
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(con!=null){
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void dbClose(Connection con){
		
		if(con!=null){
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static void main(String[] args) throws Exception {
		
		Connection con = null; 
		Statement stmt =null;
		ResultSet rs = null;
		try {
			con = getConnection();
			if(con == null){
				logger.error("can not create DB connection!!!");
			}else{
				stmt = con.createStatement();
				String sql="select ac.code,ac.name,ast.quantity,ast.weight, ast.id, ast.adv_address from adx_strategy ast,adx_customer ac where ast.customer_id=ac.id and ast.state=1 and ast.is_using=1";
				rs=stmt.executeQuery(sql);
				while(rs.next()){
					System.out.println((rs.getString("code")+","+rs.getString("name")+","+rs.getString("weight")+","+rs.getString("quantity")+","+rs.getString("id")+","+rs.getString("adv_address")+";"));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			dbClose(con, stmt, rs);
		}
		
	}
}
